<?php

namespace app\index\controller;

use think\Db;
use think\Exception;
use think\Validate;
use think\Controller;
use app\common\library\Compare;

class TmpMerge  extends Controller
{
    protected $layout = 'default';
    protected $noNeedLogin = [];
    protected $noNeedRight = ['*'];

    /*
     * 全匹配脚本
     */
    public function index(){
        echo '全匹配';exit;
        try {
            ini_set('memory_limit', '10240M');
            set_time_limit(0);   // 设置脚本最大执行时间 为0 永不过期
            $id = $this->request->get('id');//e1e5dcf7663d4ef8
            //计划表
            $data_tbsj_b_sichuan_pt_02_model = Db::name('data_tbsj_a_sichuan_pt_02');
            $data_tbsj_b_sichuan_pt_03_model = Db::name('data_tbsj_a_sichuan_pt_03');

            $zyjh_table = Db::name('data_zy_config')->field('bghz,zyfs_table_2020,zyfs_table_2019,zyfs_table_2018')->select();

            foreach ($zyjh_table as $v) {
                if (!empty($v['zyfs_table_2020'])) {
                    $sql = $this->getAllMatchSql(
                        'data_tbsj_' . $v['bghz']
                        ,$v['zyfs_table_2020'],
                        '2020'
                    );
                    $data_tbsj_b_sichuan_pt_02_model->execute($sql);
                }
                if (!empty($v['zyfs_table_2019'])) {
                    $sql = $this->getAllMatchSql(
                        'data_tbsj_' . $v['bghz']
                        ,$v['zyfs_table_2019'],
                        '2019'
                    );
                    $data_tbsj_b_sichuan_pt_02_model->execute($sql);
                }
                if (!empty($v['zyfs_table_2018'])) {
                    $sql = $this->getAllMatchSql(
                        'data_tbsj_' . $v['bghz']
                        ,$v['zyfs_table_2018'],
                        '2018'
                    );
                    $data_tbsj_b_sichuan_pt_02_model->execute($sql);
                }
            }

        } catch (\Exception $e) {
            var_dump($e->getMessage());
        }
    }

    public function getAllMatchSql($table,$join_table, $year){
        return  "UPDATE {$table} a
        JOIN {$join_table} b
        SET a.lqrs{$year} = b.lqrs,
        a.zgf{$year} = b.zgf,
        a.pjf{$year} = b.pjf,
        a.zdf{$year} = b.zdf,
        a.zdfpw{$year} = b.zdfpw,
        a.relevanceid{$year} = b.id,
        a.status{$year}=1
        WHERE
            a.yxmc = b.yxmc 
            AND a.zymc = b.zymc";
    }

    /**
     * 规则匹配脚本
     */
    public function fuzzyMatching(){
        echo '规则匹配';exit;
        try {
            ini_set('memory_limit', '10240M');
            set_time_limit(0);   // 设置脚本最大执行时间 为0 永不过期
//            $id = $this->request->get('id');//e1e5dcf7663d4ef8
            //计划表
//            $data_tbsj_b_sichuan_pt_02_model = Db::name('data_tbsj_a_sichuan_pt_02');
//            $data_tbsj_b_sichuan_pt_03_model = Db::name('data_tbsj_a_sichuan_pt_03');
            $compareClass = new Compare();
            //config表匹配
            $zyjh_table = Db::name('data_zy_config')->field('bghz,zyfs_table_2020,zyfs_table_2019,zyfs_table_2018')->select();
            foreach ($zyjh_table as $v) {
                if (!empty($v['zyfs_table_2020'])) {
                    $this->deal_data($v,$compareClass,'2020');
                }
                if (!empty($v['zyfs_table_2019'])) {
                    $this->deal_data($v,$compareClass,'2019');
                }
                if (!empty($v['zyfs_table_2018'])) {
                    $this->deal_data($v,$compareClass,'2018');
                }
            }
            echo 'success';
        } catch (\Exception $e) {
            var_dump($e->getMessage());
            echo '失败';exit;
        }
    }

    /**
     *  规则匹配
     */
    public function deal_data($v,$compareClass,$year){
        $model_jhb = Db::name('data_tbsj_' .$v['bghz']);
        $zyfs_table = '';
        if ($year == '2020') {
            $zyfs_table = $v['zyfs_table_2020'];
        } elseif ($year == '2019') {
            $zyfs_table = $v['zyfs_table_2019'];
        } elseif ($year == '2018') {
            $zyfs_table = $v['zyfs_table_2018'];
        }
        $model_zyfs_table_2020 = Db::name($zyfs_table);
        $data_2020_jhb = $this->getJhb_0_like_data($model_jhb,$year);
        if ($data_2020_jhb) {
            foreach ($data_2020_jhb as $v_2020_jhb) {
                $data_zyfs_2020 = $this->getZyfs_allfield_like_data($model_zyfs_table_2020,$v_2020_jhb);
                if ($data_zyfs_2020) {
                    foreach ($data_zyfs_2020 as $v_2020_zyfs) {
                        // 过滤特殊字符/字，筛选括号内的字
                        $compare_a = $v_2020_jhb['zymc'];
                        $compare_b = $v_2020_zyfs['zymc'];
                        $data = $compareClass->filtrate($compare_a, $compare_b, $v_2020_jhb,$model_jhb);
                        if (!$data) continue;
                        //匹配
                        $similar = $compareClass->getSimilar($data[0],$data[1]);
                        if ($similar>=0.8){ //相似度暂定0.8匹配
//                                        var_dump($similar);
//                                        var_dump($data);
                            //更新
                            $this->update_jhs($model_jhb, $v_2020_zyfs, $v_2020_jhb['id'], $year);
                        } else {
                            $this->update_jhs_status_3($model_jhb,$v_2020_jhb['id'], $year);
                        }
                    }
                }
            }
        }
    }



    public function getJhb_0_like_data(&$model_jhb,$year){
        return $model_jhb
            ->field('id,yxmc,zymc,zy')
            ->whereOr([
                'zymc'=> [
                    ['like',"%(%"],
                    ['like',"%（%"]
                ]
            ])
            ->where(["status{$year}"=>0])
            ->select();
    }

    //条件筛选专业分表
    public function getZyfs_allfield_like_data($model_zyfs_table_2020,$v_2020_jhb){
        $data_fs =  $model_zyfs_table_2020
            ->field("id,yxmc,zymc,lqrs,zgf,pjf,zdf,zdfpw")
            ->whereOr([
                'zymc'=> [
                    ['like',"%(%"],
                    ['like',"%（%"]
                ]
            ])
            ->where([
                'yxmc'=> [
                    ['eq',$v_2020_jhb['zy']],
                    ['eq',$v_2020_jhb['yxmc'],],
                    'or'
                ]
            ])
            ->select();
        return $data_fs;
    }

    public function update_jhs($model_jhb, $v_2020_zyfs, $id, $year){
        return $model_jhb->where([
            'id' => $id
        ])->update([
            "lqrs{$year}" => $v_2020_zyfs["lqrs"],
            "zgf{$year}" => $v_2020_zyfs["zgf"],
            "pjf{$year}" => $v_2020_zyfs["pjf"],
            "zdf{$year}" => $v_2020_zyfs["zdf"],
            "zdfpw{$year}" => $v_2020_zyfs["zdfpw"],
            "status{$year}" => 2,
            "relevanceid{$year}" => $v_2020_zyfs['id']
        ]);
    }

    //规则匹配不成功的
    public function update_jhs_status_3($model_jhb, $id, $year){
        return $model_jhb->where([
            'id' => $id
        ])->update([
            "status{$year}" => 3,
        ]);
    }

}
